#include "dbdata.h"
#include <QCoreApplication>
#include <QDebug>
#include <QSqlQuery>
#include <QSqlError>

using namespace ns_yht;

DbData *DbData::g_dbData = nullptr;

DbData::DbData() : m_dbType(DBType::DTSqllite)
{
    if (QSqlDatabase::contains("qt_sql_data_connection"))
        m_dbData = QSqlDatabase::database("qt_sql_data_connection");
    else
        m_dbData = QSqlDatabase::addDatabase("QSQLITE", "qt_sql_data_connection");

    m_dbData.setDatabaseName(QCoreApplication::applicationDirPath() + "/yhtdic.db");

    if (m_dbData.open())
    {
        // if (GLOG)
        //     GLOG->info("dbData open sqlite ok!");
    }
    else
    {
        // if (GLOG)
        //     GLOG->info("dbData open sqlite error!");
    }

    if (!g_dbData)
        g_dbData = this;
}

DbData::DbData(const QString &host, const QString &database, const QString &uid, const QString &pwd)
    : m_dbType(DBType::DTSqlserver)
{
    if (QSqlDatabase::contains("qt_sql_data_connection"))
    {
        m_dbData = QSqlDatabase::database("qt_sql_data_connection");
    }
    else
    {
        m_dbData = QSqlDatabase::addDatabase("QODBC", "qt_sql_data_connection");
    }
    m_dbData.setDatabaseName(QString("DRIVER={SQL SERVER};SERVER=%1;DATABASE=%2;UID=%3;PWD=%4;")
                                 .arg(host, database, uid, pwd));
    if (m_dbData.open())
        qInfo() << "dbData open sqlserver ok!";
    else
        qInfo() << "dbData open sqlserver error!" << host << database;
    g_dbData = this;
}

DbData::~DbData()
{
    if (m_dbData.isOpen())
        m_dbData.close();
    g_dbData = nullptr;
}

DbData *DbData::instance()
{
    return g_dbData;
}

QSqlDatabase &DbData::getDb()
{
    return m_dbData;
}

bool DbData::isOk() const
{
    return m_dbData.isOpen();
}

DBType DbData::getDbType() const
{
    return m_dbType;
}

//====================================================================================================

bool DbData::checkTFishingArea()
{
    if (!m_dbData.isOpen())
        return false;

    QSqlQuery query(m_dbData);
    QString strsql = QString::fromLocal8Bit("create table if not exists TMainFishingArea(code TEXT NOT NULL PRIMARY KEY, area TEXT, centerLat real, centerLon real);");
    if (!query.exec(strsql))
    {
        QSqlError str = query.lastError();
        QString strerror = str.text();
        qDebug() << "checkTFishingArea.lastError()1----------------------" << strerror;
        return false;
    }

    strsql = QString::fromLocal8Bit("create table if not exists TSmallFishingArea(code TEXT NOT NULL PRIMARY KEY, area TEXT, centerLat real, centerLon real);");
    if (!query.exec(strsql))
    {
        QSqlError str = query.lastError();
        QString strerror = str.text();
        qDebug() << "checkTFishingArea.lastError()2----------------------" << strerror;
        return false;
    }

    return true;
}

QString DbData::getCountry(int id)
{
    if (!m_dbData.isOpen())
        return "";

    QSqlQuery query(m_dbData);
    QString str = QString("select schinese from TCountry where id = %1").arg(id);
    query.prepare(str);

    if (!query.exec())
    {
        qDebug() << "getCountry-------" << query.lastError();
        return "";
    }

    if (query.next())
    {
        return query.value("schinese").toString();
    }

    return "";
}

bool DbData::getCountryList(QStringList &sList)
{
    if (!m_dbData.isOpen())
        return false;

    QSqlQuery query(m_dbData);
    QString str = QString("select schinese from TCountry order by id");
    query.prepare(str);

    if (!query.exec())
    {
        qDebug() << "getCountryList-------" << query.lastError();
        return false;
    }

    sList.clear();
    while (query.next())
    {
        sList.append(query.value("schinese").toString());
    }

    return true;
}

/**
 * @brief  获取船舶静态信息，这个查询比较频繁，目前看没问题，后续如果性能影响明显，可以考虑内存数据库
 *
 * @param mmsi
 * @param enName
 * @param cnName
 * @param nationality
 * @return true
 * @return false
 */
bool DbData::getShipInfo(qint64 mmsi, QString &enName, QString &cnName, QString &nationality, int &typeId)
{
    if (!m_dbData.isOpen())
        return false;

    QSqlQuery query(m_dbData);
    QString str = QString("select enname,cnname,nationality,type_id from TShip where mmsi = %1").arg(mmsi);
    query.prepare(str);

    if (!query.exec())
    {
        qDebug() << "getShipInfo-------" << query.lastError();
        return false;
    }

    if (query.next())
    {
        enName = query.value("enname").toString();
        cnName = query.value("cnname").toString();
        nationality = query.value("nationality").toString();
        typeId = query.value("type_id").toInt();
        return true;
    }
    else
        return false;
}

/**
 * @brief 船舶模糊查询
 *
 * @param key
 * @param shipList
 * @return true
 * @return false
 */
bool DbData::getShipList(const QString &key, QVector<QPair<quint64, QString>> &shipList)
{
    if (!m_dbData.isOpen())
        return false;

    QSqlQuery query(m_dbData);
    QString str = QString("select mmsi,enname,cnname from TShip where (mmsi like :key) or (enname like  :key) or (cnname like :Key) limit 11");
    query.prepare(str);
    query.bindValue(":key", "%" + key + "%");

    if (!query.exec())
    {
        qDebug() << "getShipList-------" << query.lastError();
        return false;
    }

    shipList.clear();
    while (query.next())
    {
        QString enname = query.value("enname").toString();
        QString cnname = query.value("cnname").toString();
        QString shipname = (cnname == "" ? enname : cnname);
        shipList.append(QPair<qint64, QString>(query.value("mmsi").toULongLong(), shipname));
    }
    return true;
}

bool DbData::getRiskArea(QVector<ModelRiskArea> &areaList)
{
    if (!m_dbData.isOpen() || !checkTRiskArea())
        return false;

    QSqlQuery query(m_dbData);
    QString str = QString("select * from TRiskArea");
    query.prepare(str);

    if (!query.exec())
    {
        return false;
    }
    areaList.clear();
    while (query.next())
    {
        ModelRiskArea model;
        model.id = query.value("id").toInt();
        model.name = query.value("aname").toString();
        model.geom = query.value("geom").toString();
        model.radius = query.value("radius").toDouble();
        model.extension = query.value("extension").toString();
        model.type = query.value("atype").toInt();
        areaList.append(model);
    }
    return true;
}

bool DbData::saveRiskArea(const QVector<ModelRiskArea> &areaList)
{
    if (!m_dbData.isOpen() || !checkTRiskArea())
        return false;
    m_dbData.transaction();

    QSqlQuery query(m_dbData);
    query.prepare("delete from TRiskArea");
    if (!query.exec())
    {
        m_dbData.rollback();
        return false;
    }
    foreach (const auto &model, areaList)
    {
        query.prepare("insert into TRiskArea(aname,geom,extension,radius,atype) values(:aname,:geom,:extension,:radius,:atype)");
        query.bindValue(":aname", model.name);
        query.bindValue(":geom", model.geom);
        query.bindValue(":extension", model.extension);
        query.bindValue(":radius", model.radius);
        query.bindValue(":atype", model.type);
        if (!query.exec())
        {
            m_dbData.rollback();
            return false;
        }
    }
    return m_dbData.commit();
}

bool DbData::checkTRiskArea()
{
    if (!m_dbData.isOpen())
        return false;

    QSqlQuery query(m_dbData);
    QString strsql = QString::fromLocal8Bit("create table if not exists TRiskArea(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, aname TEXT, geom TEXT,extension TEXT, radius real, atype int);");
    if (!query.exec(strsql))
    {
        return false;
    }

    return true;
}
